﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using SaludOcupacional.Exceptions;
using System.Collections.ObjectModel;
using System.Globalization;
using SaludOcupacional.Model;


namespace SaludOcupacional.DataAccess
{
    public class VistaReportesDataAccess : IDisposable
    {

        private DatabaseManager dbManager;

        public void Dispose()
        {
            dbManager.Dispose();
        }
        public VistaReportesDataAccess()
        {
            dbManager = new DatabaseManager();
        }

        public Collection<Respiratorio> TodoRespiratorio()
        {


            string sqlsentence = "SELECT * FROM [AnalisisRespiratorio] ";

            dbManager.Command.CommandText = sqlsentence;


            Collection<Respiratorio> lista = new Collection<Respiratorio>();

            try
            {
                DataSet dataSet = dbManager.Query();

                if (dataSet.Tables[0].Rows.Count > 0)
                {
                    DataRowCollection reader = dataSet.Tables[0].Rows;

                    for (int i = 0; i < dataSet.Tables[0].Rows.Count; i++)
                    {

                        lista.Add(new Respiratorio
                        {
                            CodAnalisis =
                                Convert.ToInt32(reader[i]["cod_analisis"], System.Globalization.CultureInfo.InvariantCulture),
                            CiEmpleado =
                            Convert.ToInt32(reader[i]["ci_empleado"], System.Globalization.CultureInfo.InvariantCulture),


                            Radiografia = reader[i]["radiografia_torax"].ToString(),
                            Obstructiva = reader[i]["grado_obstructiva"].ToString(),
                            Restrictiva =
                                reader[i]["grado_restrictiva"].ToString(),

                            Nota = reader[i]["nota_radiografia"].ToString(),
                            Accion = reader[i]["accion_atomar"].ToString(),
                            Observaciones = reader[i]["observaciones"].ToString(),
                            Fecha =
                                reader[i]["fecha"].ToString()


                        });
                    }
                }
            }


            catch (Exception)
            {
                throw;
            }

            return lista;

        }

        public Collection<Audiometrico> TodoAudiometrico()
        {


            string sqlsentence = "SELECT * FROM [AnalisisAudiometrico] ";

            dbManager.Command.CommandText = sqlsentence;


            Collection<Audiometrico> lista = new Collection<Audiometrico>();

            try
            {
                DataSet dataSet = dbManager.Query();

                if (dataSet.Tables[0].Rows.Count > 0)
                {
                    DataRowCollection reader = dataSet.Tables[0].Rows;

                    for (int i = 0; i < dataSet.Tables[0].Rows.Count; i++)
                    {

                        lista.Add(new Audiometrico
                        {
                            CodAnalisis =
                                Convert.ToInt32(reader[i]["cod_analisis"], System.Globalization.CultureInfo.InvariantCulture),
                            CiEmpleado =
                            Convert.ToInt32(reader[i]["ci_empleado"], System.Globalization.CultureInfo.InvariantCulture),


                            Izq = reader[i]["oido_izq"].ToString(),
                            Der = reader[i]["oido_der"].ToString(),
                            Bilateral =
                                reader[i]["bilateral"].ToString(),

                            Acciones = reader[i]["acciones_atomar"].ToString(),
                            Observaciones = reader[i]["observaciones"].ToString(),
                            Fecha =
                                reader[i]["fecha"].ToString()


                        });
                    }
                }
            }


            catch (Exception)
            {
                throw;
            }

            return lista;

        }

        public Collection<Oftalmologico> TodoOftalmologico()
        {


            string sqlsentence = "SELECT * FROM [AnalisisOftalmologico] ";

            dbManager.Command.CommandText = sqlsentence;


            Collection<Oftalmologico> lista = new Collection<Oftalmologico>();

            try
            {
                DataSet dataSet = dbManager.Query();

                if (dataSet.Tables[0].Rows.Count > 0)
                {
                    DataRowCollection reader = dataSet.Tables[0].Rows;

                    for (int i = 0; i < dataSet.Tables[0].Rows.Count; i++)
                    {

                        lista.Add(new Oftalmologico
                        {
                            CodAnalisis =
                                Convert.ToInt32(reader[i]["cod_analisis"], System.Globalization.CultureInfo.InvariantCulture),
                            CiEmpleado =
                            Convert.ToInt32(reader[i]["ci_empleado"], System.Globalization.CultureInfo.InvariantCulture),


                            UsoLentes = reader[i]["uso_lentes"].ToString(),
                            OiSin = reader[i]["oi_sin"].ToString(),
                            OdSin =
                                reader[i]["od_sin"].ToString(),

                            OiCon = reader[i]["io_con"].ToString(),
                            OdCon = reader[i]["od_con"].ToString(),
                            Miopia = reader[i]["miopia"].ToString(),
                            Astigmatismo = reader[i]["astigmatismo"].ToString(),
                            Hipermetropia = reader[i]["hipermetropia"].ToString(),
                            Pterigiun = reader[i]["pterigiun"].ToString(),
                            Pinguecula = reader[i]["pinguecula"].ToString(),
                            Seudofaquia = reader[i]["seudofaquia"].ToString(),
                            Daltonismo = reader[i]["daltonismo"].ToString(),
                            Normal = reader[i]["normal"].ToString(),
                            Accion = reader[i]["acciones_atomar"].ToString(),

                            Observaciones = reader[i]["observaciones"].ToString(),
                            Fecha =
                                reader[i]["fecha"].ToString()


                        });
                    }
                }
            }


            catch (Exception)
            {
                throw;
            }

            return lista;

        }

        public Collection<Laboratorial> TodoLaboratorial()
        {


            string sqlsentence = "SELECT * FROM [DiagnosticoLaboratorial] ";

            dbManager.Command.CommandText = sqlsentence;


            Collection<Laboratorial> lista = new Collection<Laboratorial>();

            try
            {
                DataSet dataSet = dbManager.Query();

                if (dataSet.Tables[0].Rows.Count > 0)
                {
                    DataRowCollection reader = dataSet.Tables[0].Rows;

                    for (int i = 0; i < dataSet.Tables[0].Rows.Count; i++)
                    {

                        lista.Add(new Laboratorial
                        {
                            CodAnalisis =
                                Convert.ToInt32(reader[i]["cod_diagnostico"], System.Globalization.CultureInfo.InvariantCulture),
                            CiEmpleado =
                            Convert.ToInt32(reader[i]["ci_empleado"], System.Globalization.CultureInfo.InvariantCulture),


                            Widal = reader[i]["widal"].ToString(),
                            Chagas = reader[i]["chagas"].ToString(),
                            Diabetes =
                                reader[i]["diabetes"].ToString(),

                            Hepatitis = reader[i]["hepatitis"].ToString(),
                            Vih = reader[i]["vih"].ToString(),
                            Plomo = Convert.ToInt64(reader[i]["plomo_sangre"], System.Globalization.CultureInfo.InvariantCulture),
                            Xileno = Convert.ToInt64(reader[i]["xileno"], System.Globalization.CultureInfo.InvariantCulture),
                            Tolueno = Convert.ToInt64(reader[i]["tolueno"], System.Globalization.CultureInfo.InvariantCulture),
                            Orina = reader[i]["analisis_orina"].ToString(),
                            Hb = Convert.ToInt64(reader[i]["hb"], System.Globalization.CultureInfo.InvariantCulture),
                            Hto = Convert.ToInt64(reader[i]["hto"], System.Globalization.CultureInfo.InvariantCulture),
                            Glicemia = Convert.ToInt64(reader[i]["glicemia"], System.Globalization.CultureInfo.InvariantCulture),
                            Colesterol = Convert.ToInt64(reader[i]["colesterol"], System.Globalization.CultureInfo.InvariantCulture),
                            Trigliceridos = Convert.ToInt64(reader[i]["trigliceridos"], System.Globalization.CultureInfo.InvariantCulture),
                            AcidoUrico = Convert.ToInt64(reader[i]["acido_urico"], System.Globalization.CultureInfo.InvariantCulture),
                            Vdrl = reader[i]["vdrl"].ToString(),
                            Creatina = Convert.ToInt64(reader[i]["creatina"], System.Globalization.CultureInfo.InvariantCulture),
                            Observaciones = reader[i]["observaciones"].ToString(),
                            Accion = reader[i]["acciones_atomar"].ToString(),

                            
                            Fecha =
                                reader[i]["fecha"].ToString()


                        });
                    }
                }
            }


            catch (Exception)
            {
                throw;
            }

            return lista;

        }

        public Collection<Carnet> TodoCarnet()
        {


            string sqlsentence = "SELECT * FROM [CarnetSanitario] ";

            dbManager.Command.CommandText = sqlsentence;


            Collection<Carnet> lista = new Collection<Carnet>();

            try
            {
                DataSet dataSet = dbManager.Query();

                if (dataSet.Tables[0].Rows.Count > 0)
                {
                    DataRowCollection reader = dataSet.Tables[0].Rows;

                    for (int i = 0; i < dataSet.Tables[0].Rows.Count; i++)
                    {

                        lista.Add(new Carnet
                        {
                            CodCarnet =
                                Convert.ToInt32(reader[i]["cod_carnet"], System.Globalization.CultureInfo.InvariantCulture),
                            CiEmpleado =
                            Convert.ToInt32(reader[i]["ci_empleado"], System.Globalization.CultureInfo.InvariantCulture),


                            Vdrl = reader[i]["vdrl"].ToString(),
                            Ascaris = reader[i]["ascaris"].ToString(),
                            Amebas =
                                reader[i]["amebas"].ToString(),

                            Giardias = reader[i]["giardias"].ToString(),
                            Nematodos = reader[i]["nematodos"].ToString(),
                            Trematodos = reader[i]["trematodos"].ToString(),
                            Trichuris = reader[i]["trichuris"].ToString(),
                            OtrosParasitos = reader[i]["otros_parasitos"].ToString(),
                            SinParasitos = reader[i]["sin_parasitos"].ToString(),


                            Acciones = reader[i]["acciones_atomar"].ToString(),
                            Observaciones = reader[i]["observaciones"].ToString(),
                            Fecha =
                                reader[i]["fecha"].ToString()


                        });
                    }
                }
            }


            catch (Exception)
            {
                throw;
            }

            return lista;

        }
    }
}
